/**
 * Created by Administrator on 2017/07/27.
 */


var common = require('../../common');
var pool = require('../index');
var mysql = require('mysql');
var ItbBaseModel = require('./ItbBaseModel');
var moment = require('moment');

// Schema 结构
var ItbPartnerSaleRankModel = function(obj){
    ItbBaseModel.call(this,obj);

  //`rank_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  //`partner_id` bigint(20) NOT NULL COMMENT '合伙人id',
  //`prov` bigint(20) DEFAULT NULL COMMENT '省',
  //`city` bigint(20) DEFAULT NULL COMMENT '市',
  //`dist` bigint(20) DEFAULT NULL COMMENT '区',
  //`year` int(4) NOT NULL COMMENT '年',
  //`month` int(2) NOT NULL COMMENT '月',
  //`sale_fee` bigint(20) NOT NULL DEFAULT '0' COMMENT '销售金额（单位分）：合伙人下线的订单金额（除去邮费和优惠额）',
  //`income` bigint(20) NOT NULL DEFAULT '0' COMMENT '合伙人收入（单位分）',
  //`desc` text COMMENT '备注',
  //`create_time` datetime NOT NULL,
  //`update_time` datetime NOT NULL,
  //`update_by` varchar(50) NOT NULL DEFAULT '',
  //`del_flg` int(1) NOT NULL DEFAULT '0' COMMENT '删除flg',
};

ItbPartnerSaleRankModel.prototype = new ItbBaseModel();

// 取得表名
ItbPartnerSaleRankModel.prototype.getTableName = function(){
    return 'itb_partner_sale_rank';
};
// 取得查询语句中where 后面匹配主键的条件（需要各派生类进行重载）
ItbPartnerSaleRankModel.prototype.getWhereConditionOfKeyEqual = function(){
    return 'rank_id='+this.rank_id;
};

//-------------------------------------------------------------------------------
// fmt: 'YYYY-MM-DD'
function getFirstDay(yyyy,mm,fmt) {
    var dt = new Date(yyyy,mm-1,1);// Date的月份下标从0开始
    var mt = moment(dt);
    return mt.format(fmt);
}
function getLastDay(yyyy,mm,fmt) {
    var dt = new Date(yyyy,mm,1);// 下个月第一天
    var cdt = new Date(dt.getTime()-1000*3600*24);
    var mt = moment(cdt);
    return mt.format(fmt);
}

//-------------------------------------------------------------------------------
/**
 * 新建指定月份指定地区的销售信息
 * */
ItbPartnerSaleRankModel.prototype.insMonthAreaSaleInfo = function (callback, userId, y, m, prov, city, dist) {
    /**********************************************
     * 1、数据定期更新，并非实时
     * 2、城市合伙人的销售奖金之类的收入不参与排行
     * 3、高级城市合伙人的额外提成之类的收入不参与排行
     * 4、高级合伙人作为普通合伙人的角色参与排行
     **********************************************/

    var year = Number(y||'0')||0;
    var month = Number(m||'0')||0;
    if(year<2016 || month<1 || month>12) {
        return callback('参数错误', null);
    }

    var stDt = getFirstDay(year, month, 'YYYY-MM-DD')+' 00:00:00';
    var edDt = getLastDay(year, month, 'YYYY-MM-DD')+' 23:59:59';

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbPartnerSaleRankModel.js [insMonthAreaSaleInfo] Err:'+err);
            return callback(err,null);
        }
        var sql =
            " INSERT INTO itb_partner_sale_rank "+
            " (`year` "+
            " ,`month` "+
            " ,`prov` "+
            " ,`city` "+
            " ,`dist` "+
            " ,`partner_id` "+
            " ,`sale_fee` "+
            " ,`income` "+
            " ,`desc` "+
            " ,`create_time` "+
            " ,`update_time` "+
            " ,`update_by` "+
            " ,`del_flg`) "+

            " (SELECT "+year+
            "     ,"+month+
            "     ,x.prov "+
            "     ,x.city "+
            "     ,x.dist "+
            "     ,x.partner_id "+
            "     ,ceil(IFNULL(SUM(x.fee),0)) AS sale_fee "+
            "     ,ceil(IFNULL(SUM(x.income),0)) AS income "+
            "     ,'AUTO INSERT' "+
            "     ,sysdate() "+
            "     ,sysdate() "+
            "     ,"+userId+
            "     ,0 "+
            " FROM ( "+

            " SELECT p.partner_id, a.prov, a.city, a.dist, "+
            "     o.order_id, "+
            "     o.total_fee-SUM(d.mail_fee) AS fee, "+// 销售额(除去邮费和优惠金额)
            "     func_calc_partner_income(o.order_id, 10) AS income "+
            " FROM itb_partner p, "+
            "     itb_partner_cust c, "+
            "     itb_partner_application a, "+
            "     itb_trade_order o, "+
            "     itb_trade_order_detail d "+
            " WHERE p.del_flg=0 "+
            " AND c.del_flg=0 AND c.partner_id=p.partner_id "+
            " AND a.del_flg=0 AND a.id=p.apply_id "+// 依据申请地址
            " AND a.prov="+prov+
            " AND a.city="+city+
            " AND a.dist="+dist+
            " AND o.del_flg=0 AND o.state=40 AND o.type=0 AND o.user_id=c.user_id "+// 完成的普通订单
            " AND o.create_time>=c.start_time "+
            " AND o.create_time<=c.end_time "+
            " AND o.finish_time >= '"+stDt+"' "+
            " AND o.finish_time <= '"+edDt+"' "+
            " AND d.del_flg=0 AND d.order_id=o.order_id "+
            " GROUP BY o.order_id ) x "+
            " GROUP BY x.partner_id ) ";

        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            callback(error, result);
        });
    });
};

//-------------------------------------------------------------------------------
/**
 * 删除指定月份指定地区的销售信息
 * */
ItbPartnerSaleRankModel.prototype.delMonthAreaSaleInfo = function (callback, userId, y, m, prov, city, dist) {
    var year = Number(y||'0')||0;
    var month = Number(m||'0')||0;
    if(year<2016 || month<1 || month>12) {
        return callback('参数错误', null);
    }

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbPartnerSaleRankModel.js [delMonthAreaSaleInfo] Err:'+err);
            return callback(err,null);
        }
        var sql =
            " UPDATE itb_partner_sale_rank t " +
            " SET t.del_flg=1, t.update_time=sysdate(), t.update_by="+userId+
            " WHERE t.del_flg=0 "+
            " AND t.year="+year+
            " AND t.month="+month+
            " AND t.prov="+prov+
            " AND t.city="+city+
            " AND t.dist="+dist;

        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            callback(error, result);
        });
    });
};

//-------------------------------------------------------------------------------
// 获取指定月的排行
// bNow: true时获取当前月的排行, false时获取指定月的排行year/month
function sql_getCityPartnerMonthRankList(bForCnt, prov, city, dist, bNow, year, month){

    var sql ='';
    if(bForCnt) {
        sql+= " SELECT COUNT(*) AS count ";
    } else {
        if(bNow) {
            sql+= " SELECT x.* "+
                " ,DATE_FORMAT(now(), '%Y/%m/01') AS stDt "+
                " ,DATE_FORMAT(now(), '%Y/%m/%d') AS edDt ";
        } else {
            var stDt = getFirstDay(year, month, 'YYYY/MM/DD');
            var edDt = getLastDay(year, month, 'YYYY/MM/DD');
            sql+= " SELECT x.* "+
                " ,'"+stDt+"' AS stDt "+
                " ,'"+edDt+"' AS edDt ";
        }
    }

    sql += " FROM ( "+
        " SELECT t.*, u.user_id, u.mobile "+
        " FROM itb_partner_sale_rank t "+
        "     ,itb_partner p "+
        "     ,itb_user u "+
        " WHERE t.del_flg=0 ";
    if(bNow) {
        sql += " AND t.year=DATE_FORMAT(now(),'%Y') "+
            " AND t.month=DATE_FORMAT(now(),'%c') ";
    } else {
        sql += " AND t.year="+year+
        " AND t.month="+month;
    }
    sql += " AND t.prov="+prov+
        " AND t.city="+city+
        " AND t.dist="+dist+
        " AND p.del_flg=0 AND p.partner_id=t.partner_id "+
        " AND u.del_flg=0 AND u.user_id=p.user_id "+
        " ORDER BY t.sale_fee DESC, t.income DESC ) x ";

    return sql;
};
//-------------------------------------------------------------------------------
// 城市合伙人查看在当前月内的、自己注册地址所在区内的所有城市合伙人的销售额（城市合伙人的绑定用户的除去邮费的订单的总额）排行
ItbPartnerSaleRankModel.prototype.getCityPartnerLatestRankList = function(prov, city, dist, pageNum, pageSize, callback){
    //
    var pgSize = Number(pageSize||'0')||0;
    var pgNum = Number(pageNum||'0')||0;
    var skipCnt = pgSize*pgNum;

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbPartnerSaleRankModel.js [getCityPartnerLatestRankList] Err:'+err);
            return callback(err,null);
        }
        var sql = sql_getCityPartnerMonthRankList(false, prov, city, dist, true);

        // 分页
        if(pgSize>0){
            sql += " LIMIT " + skipCnt + "," + pgSize;
        }

        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            if(error) {
                return callback(error, null);
            }

            if(result && result.length>0) {
                for(var i=0;i<result.length;i++){
                    var item = result[i];
                    item.rank = skipCnt+i+1;
                }
            }
            return callback(null, result);
        });
    });
};
ItbPartnerSaleRankModel.prototype.getCityPartnerLatestRankListCnt = function(prov, city, dist, callback){
    //
    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbPartnerSaleRankModel.js [getCityPartnerLatestRankListCnt] Err:'+err);
            return callback(err,null);
        }
        var sql = sql_getCityPartnerMonthRankList(true, prov, city, dist, true);

        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            if(result && result.length>0){
                callback(error, result[0].count);
            } else{
                callback(error, 0);
            }
        });
    });
};

// 获取指定月排行
ItbPartnerSaleRankModel.prototype.getCityPartnerMonthRankList = function(prov, city, dist, year, month, pageNum, pageSize, callback){
    //
    var pgSize = Number(pageSize||'0')||0;
    var pgNum = Number(pageNum||'0')||0;
    var skipCnt = pgSize*pgNum;

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbPartnerSaleRankModel.js [getCityPartnerMonthRankList] Err:'+err);
            return callback(err,null);
        }
        var sql = sql_getCityPartnerMonthRankList(false, prov, city, dist, false, year, month);

        // 分页
        if(pgSize>0){
            sql += " LIMIT " + skipCnt + "," + pgSize;
        }

        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            if(error) {
                return callback(error, null);
            }

            if(result && result.length>0) {
                for(var i=0;i<result.length;i++){
                    var item = result[i];
                    item.rank = skipCnt+i+1;
                }
            }
            return callback(null, result);
        });
    });
};
ItbPartnerSaleRankModel.prototype.getCityPartnerMonthRankListCnt = function(prov, city, dist, year, month, callback){
    //
    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbPartnerSaleRankModel.js [getCityPartnerMonthRankListCnt] Err:'+err);
            return callback(err,null);
        }
        var sql = sql_getCityPartnerMonthRankList(true, prov, city, dist, false, year, month);

        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            if(result && result.length>0){
                callback(error, result[0].count);
            } else{
                callback(error, 0);
            }
        });
    });
};

//-------------------------------------------------------------------------------
/**
 * 更新rank
 * rank<=0 时更新为默认值 999999
 * */
ItbPartnerSaleRankModel.prototype.updRankNumById = function (userId, rankId, rankNum, callback) {
    var r = Number(rankNum||'0')||0;
    if(r<=0) {
        r = 999999;
    }

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbPartnerSaleRankModel.js [updRankNumById] Err:'+err);
            return callback(err,null);
        }
        var sql =
            " UPDATE itb_partner_sale_rank t " +
            " SET t.rank="+r+
            "   , t.update_time=sysdate(), t.update_by="+userId+
            " WHERE t.del_flg=0 "+
            " AND t.rank_id="+rankId;

        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            callback(error, result);
        });
    });
};

//-------------------------------------------------------------------------------
/**
 * 更新指定月份指定地区的销售排名编号
 * */
ItbPartnerSaleRankModel.prototype.updMonthAreaSaleRankNum = function (callback, userId, y, m, prov, city, dist) {
    var year = Number(y||'0')||0;
    var month = Number(m||'0')||0;
    if(year<2016 || month<1 || month>12) {
        return callback('参数错误', null);
    }

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbPartnerSaleRankModel.js [updMonthAreaSaleRankNum] Err:'+err);
            return callback(err,null);
        }

        var sql_0 = " SELECT @rownum:=@rownum+1 AS rank, x.rank_id "+
            " FROM (SELECT @rownum:=0) rn, "+
            " ( SELECT t.* "+
            " FROM itb_partner_sale_rank t "+
            " WHERE t.del_flg=0 "+
            " AND t.year="+year+
            " AND t.month="+month+
            " AND t.prov="+prov+
            " AND t.city="+city+
            " AND t.dist="+dist+
            " ) x "+
            " ORDER BY x.sale_fee DESC, x.income DESC ";

        var sql =
            " UPDATE itb_partner_sale_rank r, ( "+sql_0+" ) tt " +
            " SET r.rank=tt.rank "+
            " WHERE r.del_flg=0 "+
            " AND r.rank_id=tt.rank_id ";

        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            callback(error, result);
        });
    });
};

//-------------------------------------------------------------------------------
// 获取月销售冠军
function sql_getTopRankHis(bForCnt, prov, city, dist, year, month){

    var sql ='';
    if(bForCnt) {
        sql+= " SELECT COUNT(*) AS count ";
    } else {
        var stDt = getFirstDay(year, month, 'YYYY/MM/DD');
        var edDt = getLastDay(year, month, 'YYYY/MM/DD');
        sql+= " SELECT x.* "+
            " ,'"+stDt+"' AS stDt "+
            " ,'"+edDt+"' AS edDt ";
    }

    sql += " FROM ( "+
        " SELECT t.*, u.user_id, u.mobile "+
        " FROM itb_partner_sale_rank t "+
        "     ,itb_partner p "+
        "     ,itb_user u "+
        " WHERE t.del_flg=0 "+
        " AND t.rank=1 ";// 冠军
    if(year>=2016 && month>=1 && month<=12) {
        sql += " AND t.year="+year+
            " AND t.month="+month;
    }
    sql += " AND t.prov="+prov+
        " AND t.city="+city+
        " AND t.dist="+dist+
        //" AND p.del_flg=0 "+// TODO 可能已不是合伙人
        " AND p.partner_id=t.partner_id "+
        " AND u.del_flg=0 "+
        " AND u.user_id=p.user_id "+
        " ORDER BY t.year DESC, t.month DESC ) x ";

    return sql;
};
//-------------------------------------------------------------------------------
// 城市合伙人查看每个月、自己注册地址所在区内的所有城市合伙人的销售额（城市合伙人的绑定用户的除去邮费的订单的总额）的销售冠军的历史
ItbPartnerSaleRankModel.prototype.getTopRankHis = function(prov, city, dist, pageNum, pageSize, callback){
    //
    var pgSize = Number(pageSize||'0')||0;
    var pgNum = Number(pageNum||'0')||0;
    var skipCnt = pgSize*pgNum;

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbPartnerSaleRankModel.js [getTopRankHis] Err:'+err);
            return callback(err,null);
        }
        var sql = sql_getTopRankHis(false, prov, city, dist, null, null);

        // 分页
        if(pgSize>0){
            sql += " LIMIT " + skipCnt + "," + pgSize;
        }

        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            if(error) {
                return callback(error, null);
            }

            return callback(null, result);
        });
    });
};
ItbPartnerSaleRankModel.prototype.getTopRankHisCnt = function(prov, city, dist, callback){
    //
    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbPartnerSaleRankModel.js [getTopRankHisCnt] Err:'+err);
            return callback(err,null);
        }
        var sql = sql_getTopRankHis(true, prov, city, dist, null, null);

        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            if(result && result.length>0){
                callback(error, result[0].count);
            } else{
                callback(error, 0);
            }
        });
    });
};

// 导出
module.exports = ItbPartnerSaleRankModel;
